package cn.yx.common.jdbc.util;

import cn.yx.common.core.enums.YesNoEnum;
import cn.yx.common.core.exception.CommonException;
import cn.yx.common.core.util.StringUtil;
import cn.yx.common.jdbc.constant.message.JdbcMetaUtilMessageKey;
import cn.yx.common.jdbc.enums.TableTypeEnum;
import cn.yx.common.jdbc.pojo.Column;
import cn.yx.common.jdbc.pojo.Table;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * <p>JDBC Meta元数据工具类</p>
 *
 * @author Wgssmart
 */
@Slf4j
public final class JdbcMetaUtil {

    private JdbcMetaUtil() {
    }

    /**
     * 通过数据源对应连接获取所有表名称
     *
     * @param dataSource
     * @return
     */
    public static List<String> getTables(DataSource dataSource) {
        try {
            return getTables(dataSource.getConnection());
        } catch (SQLException e) {
            log.error("get all table names error, {}", e.getMessage());
            throw new CommonException(JdbcMetaUtilMessageKey.GET_TABLES_ERROR, e);
        }
    }

    /**
     * 通过连接获取所有表名称
     *
     * @param connection
     * @return
     */
    public static List<String> getTables(Connection connection) {
        try {
            String schema = connection.getSchema();
            String databaseName = connection.getCatalog();
            log.info("get all table names from database: {}", databaseName);
            DatabaseMetaData databaseMetaData = connection.getMetaData();
            return getTables(schema, databaseName, databaseMetaData);
        } catch (SQLException e) {
            log.error("get all table names error, {}", e.getMessage());
            throw new CommonException(JdbcMetaUtilMessageKey.GET_TABLES_ERROR, e);
        } finally {
            JdbcUtil.close(connection);
        }
    }

    /**
     * 从DatabaseMetaData获取表名称
     *
     * @param schema
     * @param databaseName
     * @param databaseMetaData
     * @return
     */
    private static List<String> getTables(String schema, String databaseName, DatabaseMetaData databaseMetaData) {
        try (ResultSet tableResultSet = databaseMetaData.getTables(databaseName,
                schema, null, new String[]{TableTypeEnum.TABLE.getValue()})) {
            if (Objects.isNull(tableResultSet)) {
                return null;
            }
            List<String> tables = new ArrayList<>();
            while (tableResultSet.next()) {
                tables.add(tableResultSet.getString("TABLE_NAME"));
            }
            return tables;
        } catch (SQLException e) {
            log.error("get all table names error, {}", e.getMessage());
            throw new CommonException(JdbcMetaUtilMessageKey.GET_TABLES_ERROR, e);
        }
    }

    /**
     * 通过数据源对应的连接获取所有表元数据对象，可指定是否获取表字段
     *
     * @param dataSource
     * @param withColumn 是否获取表字段
     * @return
     */
    public static List<Table> getTableMeta(DataSource dataSource, boolean withColumn) {
        try {
            return getTableMeta(dataSource.getConnection(), withColumn);
        } catch (SQLException e) {
            log.error("get all table data error, {}", e.getMessage());
            throw new CommonException(JdbcMetaUtilMessageKey.GET_TABLE_META_ERROR, e);
        }
    }

    /**
     * 通过连接获取所有表元数据对象，可指定是否获取表字段
     *
     * @param connection
     * @param withColumn 是否获取表字段
     * @return
     */
    public static List<Table> getTableMeta(Connection connection, boolean withColumn) {
        try {
            String schema = connection.getSchema();
            String databaseName = connection.getCatalog();
            DatabaseMetaData databaseMetaData = connection.getMetaData();
            return getTableMeta(schema, databaseName, databaseMetaData, withColumn);
        } catch (SQLException e) {
            log.error("get all table meta error, {}", e.getMessage());
            throw new CommonException(JdbcMetaUtilMessageKey.GET_TABLE_META_ERROR, e);
        } finally {
            JdbcUtil.close(connection);
        }
    }

    /**
     * 从DatabaseMetaData获取表元数据
     *
     * @param schema
     * @param databaseName
     * @param databaseMetaData
     * @param withColumn
     * @return
     */
    private static List<Table> getTableMeta(String schema, String databaseName, DatabaseMetaData databaseMetaData, boolean withColumn) {
        log.info("get all table meta from database: {}", databaseName);
        try (ResultSet tableResultSet = databaseMetaData.getTables(databaseName,
                schema, null, new String[]{TableTypeEnum.TABLE.getValue()})) {
            if (Objects.isNull(tableResultSet)) {
                return null;
            }
            List<Table> tables = new ArrayList<>();
            while (tableResultSet.next()) {
                Table table = createTable(tableResultSet);
                if (withColumn) {
                    table.setColumnList(getColumnMeta(schema, databaseName, databaseMetaData, table.getName()));
                }
                tables.add(table);
            }
            return tables;
        } catch (SQLException e) {
            log.error("get all table meta error, {}", e.getMessage());
            throw new CommonException(JdbcMetaUtilMessageKey.GET_TABLE_META_ERROR, e);
        }
    }

    /**
     * 通过数据源对应的连接获取指定数据表的元数据对象，可指定是否获取表字段
     *
     * @param dataSource
     * @param tableName
     * @param withColumn 是否获取表字段
     * @return
     */
    public static Table getTableMeta(DataSource dataSource, String tableName, boolean withColumn) {
        try {
            return getTableMeta(dataSource.getConnection(), tableName, withColumn);
        } catch (SQLException e) {
            log.error("get table meta error, {}", e.getMessage());
            throw new CommonException(JdbcMetaUtilMessageKey.GET_TABLE_META_ERROR, e);
        }
    }

    /**
     * 通过连接获取指定数据表的元数据对象，可指定是否获取表字段
     *
     * @param connection
     * @param tableName
     * @param withColumn 是否获取表字段
     * @return
     */
    public static Table getTableMeta(Connection connection, String tableName, boolean withColumn) {
        try {
            String schema = connection.getSchema();
            String databaseName = connection.getCatalog();
            DatabaseMetaData databaseMetaData = connection.getMetaData();
            return getTableMeta(schema, databaseName, tableName, databaseMetaData, withColumn);
        } catch (SQLException e) {
            log.error("get table meta error, {}", e.getMessage());
            throw new CommonException(JdbcMetaUtilMessageKey.GET_TABLE_META_ERROR, e);
        } finally {
            JdbcUtil.close(connection);
        }
    }

    /**
     * 从DatabaseMetaData获取指定表名的元数据
     *
     * @param schema
     * @param databaseName
     * @param tableName
     * @param databaseMetaData
     * @param withColumn
     * @return
     */
    private static Table getTableMeta(String schema, String databaseName, String tableName, DatabaseMetaData databaseMetaData, boolean withColumn) {
        log.info("get table meta from database: {}, table: {}", databaseName, tableName);
        try (ResultSet tableResultSet = databaseMetaData.getTables(databaseName,
                schema, tableName, new String[]{TableTypeEnum.TABLE.getValue()})) {
            if (Objects.isNull(tableResultSet) || !tableResultSet.next()) {
                return null;
            }
            Table table = createTable(tableResultSet);
            if (withColumn) {
                table.setColumnList(getColumnMeta(schema, databaseName, databaseMetaData, tableName));
            }
            return table;
        } catch (SQLException e) {
            log.error("get table meta error, {}", e.getMessage());
            throw new CommonException(JdbcMetaUtilMessageKey.GET_TABLE_META_ERROR, e);
        }
    }

    private static Table createTable(ResultSet tableResultSet) throws SQLException {
        Table table = new Table();
        table.setName(tableResultSet.getString("TABLE_NAME"));
        table.setComment(tableResultSet.getString("REMARKS"));
        return table;
    }

    /**
     * 通过数据源对应的连接获取指定表的所有字段元数据
     *
     * @param dataSource
     * @param tableName
     * @return
     */
    public static List<Column> getColumnMeta(DataSource dataSource, String tableName) {
        try {
            return getColumnMeta(dataSource.getConnection(), tableName);
        } catch (SQLException e) {
            log.error("get column meta error, {}", e.getMessage());
            throw new CommonException(JdbcMetaUtilMessageKey.GET_COLUMN_META_ERROR, e);
        }
    }

    /**
     * 通过连接获取指定表的所有字段元数据
     *
     * @param connection
     * @param tableName
     * @return
     */
    public static List<Column> getColumnMeta(Connection connection, String tableName) {
        try {
            return getColumnMeta(connection.getSchema(), connection.getCatalog(), connection.getMetaData(), tableName);
        } catch (SQLException e) {
            log.error("get column meta error, {}", e.getMessage());
            throw new CommonException(JdbcMetaUtilMessageKey.GET_COLUMN_META_ERROR, e);
        } finally {
            JdbcUtil.close(connection);
        }
    }

    /**
     * 通过已获取到的连接相关的元数据来获取指定表的所有字段元数据
     *
     * @param schema
     * @param databaseName
     * @param databaseMetaData
     * @param tableName
     * @return
     */
    public static List<Column> getColumnMeta(String schema, String databaseName,
                                             DatabaseMetaData databaseMetaData, String tableName) {
        log.info("get column meta from database: {}, table: {}", databaseName, tableName);
        try (ResultSet columnResultSet = databaseMetaData.getColumns(databaseName, schema, tableName, "%")) {
            if (Objects.isNull(columnResultSet)) {
                return null;
            }
            List<Column> columns = new ArrayList<>();
            while (columnResultSet.next()) {
                columns.add(createColumn(columnResultSet));
            }
            List<String> primaryKeys = getPrimaryKeys(schema, databaseName, databaseMetaData, tableName);
            if (Objects.nonNull(primaryKeys)) {
                columns.stream().filter(column -> primaryKeys.contains(column.getName())).forEach(column -> column.setIsPrimaryKey(true));
            }
            return columns;
        } catch (SQLException e) {
            log.error("get column meta error, {}", e.getMessage());
            throw new CommonException(JdbcMetaUtilMessageKey.GET_COLUMN_META_ERROR, e);
        }
    }

    private static Column createColumn(ResultSet columnResultSet) throws SQLException {
        Column column = new Column();
        column.setName(columnResultSet.getString("COLUMN_NAME"));
        column.setType(columnResultSet.getInt("DATA_TYPE"));
        column.setJdbcTypeName(columnResultSet.getString("TYPE_NAME"));
        column.setColumnSize(columnResultSet.getInt("COLUMN_SIZE"));
        column.setNullable(columnResultSet.getInt("NULLABLE"));
        String isNullable = columnResultSet.getString("IS_NULLABLE");
        column.setIsNullable(YesNoEnum.YES.getValue().equals(isNullable));
        column.setComment(columnResultSet.getString("REMARKS"));
        column.setJavaTypeName(JdbcUtil.getJavaType(column.getJdbcTypeName()));
        column.setFieldName(StringUtil.underlineToCamel(column.getName()));
        return column;
    }

    /**
     * 通过数据源对应的连接获取指定表的主键
     *
     * @param dataSource
     * @param tableName
     * @return
     */
    public static List<String> getPrimaryKeys(DataSource dataSource, String tableName) {
        try {
            return getPrimaryKeys(dataSource.getConnection(), tableName);
        } catch (SQLException e) {
            log.error("get primary keys error, {}", e.getMessage());
            throw new CommonException(JdbcMetaUtilMessageKey.GET_PRIMARY_KEYS_ERROR, e);
        }
    }

    /**
     * 通过连接获取指定表的主键
     *
     * @param connection
     * @param tableName
     * @return
     */
    public static List<String> getPrimaryKeys(Connection connection, String tableName) {
        try {
            return getPrimaryKeys(connection.getSchema(), connection.getCatalog(), connection.getMetaData(), tableName);
        } catch (SQLException e) {
            log.error("get primary keys error, {}", e.getMessage());
            throw new CommonException(JdbcMetaUtilMessageKey.GET_PRIMARY_KEYS_ERROR, e);
        } finally {
            JdbcUtil.close(connection);
        }
    }

    /**
     * 通过连接的元数据获取指定表的主键
     *
     * @param schema
     * @param databaseName
     * @param databaseMetaData
     * @param tableName
     * @return
     */
    public static List<String> getPrimaryKeys(String schema, String databaseName, DatabaseMetaData databaseMetaData, String tableName) {
        log.info("get primary key from database: {}, table: {}", databaseName, tableName);
        try (ResultSet pkResultSet = databaseMetaData.getPrimaryKeys(databaseName, schema, tableName)) {
            if (Objects.isNull(pkResultSet)) {
                return null;
            }
            List<String> primaryKeys = new ArrayList<>();
            while (pkResultSet.next()) {
                primaryKeys.add(pkResultSet.getString("COLUMN_NAME"));
            }
            return primaryKeys;
        } catch (SQLException e) {
            log.error("get primary keys error, {}", e.getMessage());
            throw new CommonException(JdbcMetaUtilMessageKey.GET_PRIMARY_KEYS_ERROR, e);
        }
    }

}
